from sqlalchemy.dialects.mysql import INTEGER, VARCHAR, TIMESTAMP
from sqlalchemy import Column, Date, DateTime
from sqlalchemy.orm import Session
from datetime import datetime, timezone, timedelta

from utils.extensions import db
from utils.tool import camelToSnake, timestampToDatetime


class Dictionary(db.Model):
    id = Column(INTEGER, primary_key=True)
    dic_code = Column(VARCHAR(255), comment='字典编码')
    dic_name = Column(VARCHAR(255), comment='字典名称')
    description = Column(VARCHAR(255), comment='描述')
    created_time = Column(TIMESTAMP)
    last_modified_at = Column(TIMESTAMP, nullable=True)
    last_modified_by = Column(VARCHAR(255), comment='最后修改人')



    def getPages(cls, page, pageSize):
        total = db.session.query(cls).count()
        page = int(page)
        pageSize = int(pageSize)
        offset = int(page - 1) * pageSize
        query = db.session.query(cls).order_by(cls.created_time)
        page_data = query.offset(offset).limit(pageSize).all()
        formatted_data = [cls.getList(item) for item in page_data]

        return formatted_data, total

    def getList(self):
        return {
            'id': self.id,
            'dicCode': self.dic_code,
            'dicName': self.dic_name,
            'description': self.description,
            'createdTime': datetime.timestamp(self.created_time) * 1000,
            'lastModifiedAt': datetime.timestamp(self.last_modified_at) * 1000 if self.last_modified_at else None,
            'lastModifiedBy': self.last_modified_by,
        }

    def update(cls, id, new_data, session: Session):
        record = session.query(cls).filter_by(id=id).first()

        if record is not None:
            for key, value in new_data.items():
                snake_key = camelToSnake(key)
                if hasattr(record, snake_key):
                    if isinstance(getattr(record.__class__, snake_key).type, (DateTime, Date)):
                        value = timestampToDatetime(value / 1000)
                        print(value)
                    setattr(record, snake_key, value)
            utc_now = datetime.now(timezone.utc)
            cst_now = utc_now + timedelta(hours=8)
            record.last_modified_at = cst_now

            session.commit()
        else:
            raise ValueError(f"找不到数据： {id}")

    def add(cls, data, session: Session):
        # 创建一个新的记录实例
        new_record = cls()

        # 遍历传入的数据字典
        for key, value in data.items():
            snake_key = camelToSnake(key)
            # 检查该字段是否存在于模型中
            if hasattr(cls, snake_key):
                # 如果字段类型是DateTime或Date，转换时间戳
                if isinstance(getattr(cls, snake_key).type, (DateTime, Date)):
                    value = timestampToDatetime(value / 1000)
                # 设置字段值
                setattr(new_record, snake_key, value)

        # 设置创建时间和最后修改时间为当前CST时间
        utc_now = datetime.now(timezone.utc)
        cst_now = utc_now + timedelta(hours=8)
        new_record.created_time = cst_now
        new_record.last_modified_at = cst_now

        # 添加记录到会话并提交
        session.add(new_record)
        session.commit()

        return new_record

    def delete(cls, record_id, session: Session):
        """
        根据给定的ID删除指定的记录。

        :param cls: 数据模型类
        :param record_id: 要删除的记录的ID
        :param session: 数据库会话
        :return: 删除操作的状态(True/False)
        """
        # 查询指定ID的记录
        record = session.query(cls).get(record_id)

        # 检查记录是否存在
        if record:
            # 删除记录
            session.delete(record)
            # 提交会话以保存更改
            session.commit()
            return True
        else:
            # 记录不存在，返回False
            return False


class DictionaryItem(db.Model):
    id = Column(INTEGER, primary_key=True)
    category_id = Column(INTEGER)
    code = Column(VARCHAR(255), comment='字典编码')
    name = Column(VARCHAR(255), comment='字典名称')
    sort = Column(INTEGER, comment='排序')
    created_time = Column(TIMESTAMP)
    last_modified_at = Column(TIMESTAMP)
    last_modified_by = Column(VARCHAR(255), comment='最后修改人')

    def getPages(cls, page, pageSize, id, session: Session):
        total = session.query(cls).filter_by(category_id=id).count()
        page = int(page)
        pageSize = int(pageSize)
        offset = int(page - 1) * pageSize
        query = session.query(cls).filter_by(
            category_id=id).order_by(cls.created_time)
        page_data = query.offset(offset).limit(pageSize).all()
        formatted_data = [item.to_dict() for item in page_data]
        return formatted_data, total

    def to_dict(self):
        return {
            'id': self.id,
            'categoryId': self.category_id,
            'code': self.code,
            'name': self.name,
            'sort': self.sort,
            'createdTime': datetime.timestamp(self.created_time) * 1000,
            'lastModifiedAt': datetime.timestamp(self.last_modified_at) * 1000 if self.last_modified_at else None,
            'lastModifiedBy': self.last_modified_by,
        }

    def getList(cls, id, session: Session):
        items = session.query(cls).filter_by(category_id=id).all()
        return [item.to_dict() for item in items]
    
    def getAll(cls, session: Session):
        items = session.query(cls).all()
        return [item.to_dict() for item in items]

    def update(cls, id, new_data, session: Session):
        record = session.query(cls).filter_by(id=id).first()

        if record is not None:
            for key, value in new_data.items():
                snake_key = camelToSnake(key)
                if hasattr(record, snake_key):
                    if isinstance(getattr(record.__class__, snake_key).type, (DateTime, Date)):
                        value = timestampToDatetime(value / 1000)
                        print(value)
                    setattr(record, snake_key, value)
            utc_now = datetime.now(timezone.utc)
            cst_now = utc_now + timedelta(hours=8)
            record.last_modified_at = cst_now

            session.commit()
        else:
            raise ValueError(f"找不到数据： {id}")

    def add(cls, data, session: Session):
        # 创建一个新的记录实例
        new_record = cls()

        # 遍历传入的数据字典
        for key, value in data.items():
            snake_key = camelToSnake(key)
            # 检查该字段是否存在于模型中
            if hasattr(cls, snake_key):
                # 如果字段类型是DateTime或Date，转换时间戳
                if isinstance(getattr(cls, snake_key).type, (DateTime, Date)):
                    value = timestampToDatetime(value / 1000)
                # 设置字段值
                setattr(new_record, snake_key, value)

        # 设置创建时间和最后修改时间为当前CST时间
        utc_now = datetime.now(timezone.utc)
        cst_now = utc_now + timedelta(hours=8)
        new_record.created_time = cst_now
        new_record.last_modified_at = cst_now

        # 添加记录到会话并提交
        session.add(new_record)
        session.commit()

        return new_record

    def delete(cls, record_id, session: Session):
        """
        根据给定的ID删除指定的记录。

        :param cls: 数据模型类
        :param record_id: 要删除的记录的ID
        :param session: 数据库会话
        :return: 删除操作的状态(True/False)
        """
        # 查询指定ID的记录
        record = session.query(cls).get(record_id)

        # 检查记录是否存在
        if record:
            # 删除记录
            session.delete(record)
            # 提交会话以保存更改
            session.commit()
            return True
        else:
            # 记录不存在，返回False
            return False


class Menu(db.Model):
    __tablename__ = 'menu'
    id = Column(INTEGER, primary_key=True)
    name = Column(VARCHAR(255), comment='菜单名称')
    parent_id = Column(INTEGER, default=0, comment='父菜单ID')
    path = Column(VARCHAR(255), comment='Path')
    sort = Column(INTEGER, default=0, comment='排序号')
    icon = Column(VARCHAR(255), comment='图标')
    permission_code = Column(VARCHAR(255), comment='权限码')
    created_time = Column(TIMESTAMP)
    last_modified_at = Column(TIMESTAMP, nullable=True)  # 假设可以为NULL
    last_modified_by = Column(VARCHAR(255))

    def getList(self):
        return {
            'id': self.id,
            'name': self.name,
            'parentId': self.parent_id,
            'path': self.path,
            'sort': self.sort,
            'icon': self.icon,
            'permissionCode': self.permission_code,
            'createdTime': datetime.timestamp(self.created_time) * 1000,
            'lastModifiedAt': datetime.timestamp(self.last_modified_at) * 1000 if self.last_modified_at else None,
            'lastModifiedBy': self.last_modified_by,
        }

    def add(cls, data, session: Session):
        # 创建一个新的记录实例
        new_record = cls()

        # 遍历传入的数据字典
        for key, value in data.items():
            snake_key = camelToSnake(key)
            # 检查该字段是否存在于模型中
            if hasattr(cls, snake_key):
                # 如果字段类型是DateTime或Date，转换时间戳
                if isinstance(getattr(cls, snake_key).type, (DateTime, Date)):
                    value = timestampToDatetime(value / 1000)
                # 设置字段值
                setattr(new_record, snake_key, value)

        # 设置创建时间和最后修改时间为当前CST时间
        utc_now = datetime.now(timezone.utc)
        cst_now = utc_now + timedelta(hours=8)
        new_record.created_time = cst_now
        new_record.last_modified_at = cst_now

        # 添加记录到会话并提交
        session.add(new_record)
        session.commit()

        return new_record

    def update(cls, id, new_data, session: Session):
        record = session.query(cls).filter_by(id=id).first()

        if record is not None:
            for key, value in new_data.items():
                snake_key = camelToSnake(key)
                if hasattr(record, snake_key):
                    if isinstance(getattr(record.__class__, snake_key).type, (DateTime, Date)):
                        value = timestampToDatetime(value / 1000)
                        print(value)
                    setattr(record, snake_key, value)
            utc_now = datetime.now(timezone.utc)
            cst_now = utc_now + timedelta(hours=8)
            record.last_modified_at = cst_now

            session.commit()
        else:
            raise ValueError(f"找不到数据： {id}")

    def delete(cls, record_id, session: Session):
        # 查询指定ID的记录
        record = session.query(cls).get(record_id)

        # 检查记录是否存在
        if record:
            # 删除记录
            session.delete(record)
            # 提交会话以保存更改
            session.commit()
            return True
        else:
            # 记录不存在，返回False
            return False


class Account(db.Model):
    __tablename__ = 'account_list'
    id = Column(INTEGER, primary_key=True)
    account = Column(VARCHAR(255), unique=True, nullable=False)  # 登录用户名
    password = Column(VARCHAR(255), nullable=False)  # 密码哈希值
    email = Column(VARCHAR(255))  # 可选的邮箱地址
    phone = Column(VARCHAR(20))  # 可选的电话号码
    name = Column(VARCHAR(255))  # 姓名
    
    def getAccountById(cls, account, session: Session):
        item = session.query(cls).filter_by(account=account).first()
        if item:
            return item.to_dict()
        return None
    
    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}